﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using Dapper;
using AyuBookmark.Common.Interfaces;
using AyuBookmark.Common.Models;
using AyuBookmark.Common.Extensions;
using Microsoft.Data.Sqlite;
using System.Data;

namespace AyuBookmark.Common.Repositories
{
    /// <summary>
    /// 书签数据仓库实现
    /// </summary>
    public class BookmarkRepository : BaseRepository<Bookmark, int>, IBookmarkRepository
    {
        private const string TableName = "Bookmarks";
        private const string ImageTableName = "Images";

        private readonly string _connectionString;
        //private const string TableName = "Bookmarks";

        /// <summary>
        /// 初始化书签仓库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public BookmarkRepository(string connectionString) : base(connectionString, TableName) 
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
        }
       

        #region IBookmarkRepository 实现
        public async Task<Bookmark> GetByUrlAsync(string url)
        {
            var urlHash = ComputeSha256Hash(url);

            using var connection = await GetConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<Bookmark>(
                $@"SELECT b.*, i.LocalPath as ImagePath 
                   FROM {TableName} b
                   LEFT JOIN {ImageTableName} i ON b.ImageId = i.Id
                   WHERE b.Url = @url OR b.UrlHash = @urlHash",
                new { url, urlHash });
        }

        public async Task<IEnumerable<Bookmark>> GetByCategoryAsync(int categoryId)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryAsync<Bookmark>(
                $@"SELECT b.*, i.LocalPath as ImagePath 
                   FROM {TableName} b
                   LEFT JOIN {ImageTableName} i ON b.ImageId = i.Id
                   WHERE b.CategoryId = @categoryId",
                new { categoryId });
        }

        public async Task<int> UpdateCategoryAsync(IEnumerable<int> bookmarkIds, int categoryId)
        {
            using var connection = await GetConnectionAsync();
            return await connection.ExecuteAsync(
                $"UPDATE {TableName} SET CategoryId = @categoryId WHERE Id IN @bookmarkIds",
                new { bookmarkIds, categoryId });
        }
        #endregion

        #region 重写基础方法
        public override async Task<Bookmark> AddAsync(Bookmark bookmark)
        {
            // 计算URL哈希
            bookmark.UrlHash = ComputeSha256Hash(bookmark.Url);

            // 处理首图
            if (!string.IsNullOrEmpty(bookmark.ImagePath))
            {
                bookmark.ImageId = await SaveImageAsync(bookmark.ImagePath);
            }

            return await base.AddAsync(bookmark);
        }

        public override async Task<PaginatedResult<Bookmark>> GetPagedAsync(
            Expression<Func<Bookmark, bool>> predicate = null,
            int pageIndex = 1,
            int pageSize = 10,
            Expression<Func<Bookmark, object>> orderBy = null,
            bool isDescending = true)
        {
            var baseQuery = $@"
                SELECT b.*, i.LocalPath as ImagePath, c.Name as CategoryName, c.Color as CategoryColor
                FROM {TableName} b
                LEFT JOIN {ImageTableName} i ON b.ImageId = i.Id
                LEFT JOIN Categories c ON b.CategoryId = c.Id";

            return await GetPagedResultAsync(baseQuery, predicate, pageIndex, pageSize, orderBy, isDescending);
        }
        #endregion

        #region 私有方法
        private async Task<int?> SaveImageAsync(string imagePath)
        {
            if (string.IsNullOrEmpty(imagePath)) return null;

            var imageHash = ComputeFileSha256Hash(imagePath);

            using var connection = await GetConnectionAsync();
            var existingImage = await connection.QueryFirstOrDefaultAsync<int?>(
                $"SELECT Id FROM {ImageTableName} WHERE ContentHash = @imageHash",
                new { imageHash });

            if (existingImage.HasValue) return existingImage.Value;

            var newImageId = await connection.ExecuteScalarAsync<int>(
                $@"INSERT INTO {ImageTableName} (ContentHash, LocalPath, CreatedDate)
                   VALUES (@imageHash, @imagePath, @createdDate);
                   SELECT last_insert_rowid();",
                new { imageHash, imagePath, createdDate = DateTime.Now });

            return newImageId;
        }

        private async Task<PaginatedResult<Bookmark>> GetPagedResultAsync(
            string baseQuery,
            Expression<Func<Bookmark, bool>> predicate,
            int pageIndex,
            int pageSize,
            Expression<Func<Bookmark, object>> orderBy,
            bool isDescending)
        {
            var (whereClause, parameters) = predicate != null
                ? BuildWhereClause(predicate)
                : (string.Empty, null);

            var orderByColumn = "b.CreatedTime";
            if (orderBy != null)
            {
                if (orderBy.Body is MemberExpression memberExpression)
                {
                    orderByColumn = "b." + memberExpression.Member.Name;
                }
            }

            var sql = $@"
                {baseQuery}
                {whereClause}
                ORDER BY {orderByColumn} {(isDescending ? "DESC" : "ASC")}
                LIMIT @pageSize OFFSET @offset;

                SELECT COUNT(*) FROM {TableName} b
                {whereClause};";

            var dynamicParams = parameters?.ToDynamicParameters() ?? new DynamicParameters();
            dynamicParams.Add("pageSize", pageSize);
            dynamicParams.Add("offset", (pageIndex - 1) * pageSize);

            using var connection = await GetConnectionAsync();
            using var multi = await connection.QueryMultipleAsync(sql, dynamicParams);

            var items = await multi.ReadAsync<Bookmark>();
            var totalCount = await multi.ReadFirstAsync<int>();

            return new PaginatedResult<Bookmark>
            {
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount,
                Items = items
            };
        }

        private static string ComputeSha256Hash(string input)
        {
            using var sha = System.Security.Cryptography.SHA256.Create();
            var bytes = System.Text.Encoding.UTF8.GetBytes(input);
            var hash = sha.ComputeHash(bytes);
            return BitConverter.ToString(hash).Replace("-", "").ToLower();
        }

        private static string ComputeFileSha256Hash(string filePath)
        {
            using var sha = System.Security.Cryptography.SHA256.Create();
            using var stream = System.IO.File.OpenRead(filePath);
            var hash = sha.ComputeHash(stream);
            return BitConverter.ToString(hash).Replace("-", "").ToLower();
        }
        #endregion


        /// <inheritdoc />
        public async Task<List<Bookmark>> GetBookmarksNeedingValidationAsync()
        {
            const int maxValidationAgeDays = 30; // 最多30天验证一次
            const int batchSize = 100; // 每次处理100条

            using var connection = await GetConnectionAsync();

            return (await connection.QueryAsync<Bookmark>(
                $@"SELECT * FROM {TableName} 
                   WHERE 
                       (LastValidated IS NULL OR LastValidated < @validationThreshold) 
                       AND IsInvalid = 0
                   ORDER BY LastValidated ASC NULLS FIRST
                   LIMIT @batchSize",
                new
                {
                    validationThreshold = DateTime.Now.AddDays(-maxValidationAgeDays),
                    batchSize
                })).ToList();
        }

        /// <inheritdoc />
        public async Task MarkAsInvalidAsync(int bookmarkId)
        {
            using var connection = await GetConnectionAsync();

            await connection.ExecuteAsync(
                $@"UPDATE {TableName} 
                   SET IsInvalid = 1, 
                       LastValidated = @now,
                       InvalidReason = 'URL validation failed'
                   WHERE Id = @bookmarkId",
                new
                {
                    bookmarkId,
                    now = DateTime.Now
                });
        }

        #region 辅助方法
        private async Task<IDbConnection> GetConnectionAsync()
        {
            var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            return connection;
        }
        #endregion

    }
}